using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

using System.Data.Common;

using TestMethod.Test.Models;
namespace TestMethod.Test.Dal
{
public class testDao

{
		 
			private const string colId ="Id"; 
			private const string IdentityColumn ="Id"; 
				private const string colName ="Name";
				private const string colAge ="Age";
				private const string colBrithDay ="BrithDay";
				private const string colRemark ="Remark";
				private const string coladdress ="address";
				private static readonly string[] ColumnCollections = new[] { IdentityColumn,colName,colAge,colBrithDay,colRemark,coladdress};
        private readonly string _columnCollectionSelectString = string.Join(",", ColumnCollections);
        private static readonly string[] KeyColumns = new string[] { "Name" };
        private   readonly string WhereClause = KeyColumns.JoinFormat(" AND ", "{0} =@{0}");
		
		DbHelper dbDal=null;
      
        public testDao()
        {
            dbDal=new DbHelper();
        }

        public testDao(DbHelper dbDal)
        {
            this.dbDal = dbDal;
        }
		
		private string GetSortText(SortDefinitions sortDefinitions)
		{
			string sortDefinition;
            if(sortDefinitions!=null && sortDefinitions.SoftItems.Count>0)
            {
                sortDefinition = string.Join(" , ", sortDefinitions.SoftItems.Select(p=> string.Format("{0} {1}", p.ColumnName , (p.SortOrder==SortOrder.None)?"":p.SortOrder.ToString())));
				
				sortDefinition = string.Format(" ORDER BY {0}", sortDefinition);				
            }
			else
			{
				 sortDefinition = "ORDER BY " + string.Join(",", KeyColumns);
			}		
			return sortDefinition;
		}
		
		public List<test> GetPage( SortDefinitions sortDefinitions , int startIndex, int pageSize)
        {
		    string sqlText;
			var sortText = GetSortText(sortDefinitions);
			if(startIndex>-1 && pageSize>0)
			{
			  sqlText = GetPagedQuery(sortText, startIndex, pageSize);
			}
			else
			 sqlText = GettSelectSQL();

			  var command = dbDal.GetSqlStringCommond(sqlText);
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);            
        }
		
		private test From(DataRow dr)
        {
            test tmp = new test();
					    if(dr[colId]!=null)
			{
		    tmp.Id=(int)dr[colId];
			}
				    if(dr[colName]!=null)
			{
		    tmp.Name=(string)dr[colName];
			}
				    if(dr[colAge]!=null)
			{
		    tmp.Age=(int)dr[colAge];
			}
				    if(dr[colBrithDay]!=null)
			{
		    tmp.BrithDay=(DateTime)dr[colBrithDay];
			}
				    if(dr[colRemark]!=null)
			{
		    tmp.Remark=(string)dr[colRemark];
			}
				    if(dr[coladdress]!=null)
			{
		    tmp.address=(string)dr[coladdress];
			}
				
            return tmp;
        }

        private List<test> FromDt(DataTable dt)
        {
            List<test> list = new List<test>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(From(dr));
            }
            return list;
        }
		
		

        public List<test> GetAll()
        {

             var command = dbDal.GetSqlStringCommond(GettSelectSQL());
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);     
        }

                public test GetById(test tmp)
        {
             test tml = new test();

            var command = dbDal.GetSqlStringCommond(GetSelectSQLWithKey());
             AddIdentityColumn(command, tmp);
            DataTable dt = dbDal.ExecuteDataTable(command);
             List<test> list=FromDt(dt);
             if (list.Count > 0)
             {
                 tml = list[0];
             }
             return tml;  
        }
		
     
		
		private string GetByIdSQL()
        {
            var sql = string.Format(@"SELECT  {0}
                            FROM 
                                test
                            WHERE
                                {1}
                                       ", _columnCollectionSelectString, string.Format("{0} = @{0}",IdentityColumn));
            return sql;
        }
		        public long Insert(test test)
        { 			
			var command = dbDal.GetSqlStringCommond(InsertSQL());
		   			var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameter);
			            AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command);
			            var id =Convert.ToInt64( parameter.Value);
            return id;
			        }

		public long Insert(test test, Trans t)
        { 			
			var command = dbDal.GetSqlStringCommond(InsertSQL());
		   			var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.DbType = DbType.Int64;
            parameter.Direction = ParameterDirection.Output;
            command.Parameters.Add(parameter);
			            AddInsertParameters(command, test);
            dbDal.ExecuteNonQuery(command,t);
			            var id =Convert.ToInt64( parameter.Value);
            return id;
			        }

        public void Update(test test)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command);
        }

		 public void Update(test test,Trans t)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, test);
            dbDal.ExecuteNonQuery(command,t);
        }

        public void Delete(test test)
        {
            var command =  dbDal.GetSqlStringCommond(DeletetSQL());
			            AddIdentityColumn(command, test);
			            dbDal.ExecuteNonQuery(command);
        }

		public void Delete(test test,Trans t)
        {
            var command =  dbDal.GetSqlStringCommond(DeletetSQL());
			            AddIdentityColumn(command, test);
			            dbDal.ExecuteNonQuery(command,t);
        }
				
		private void AddInsertParameters(DbCommand command, test test)
        {
            DbParameter parameter = null;
        			    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}",colName);
			            parameter.Value = test.Name;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}",colAge);
			            parameter.Value = test.Age;
            			command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}",colBrithDay);
						parameter.Value = (object)test.BrithDay??DBNull.Value;
						command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}",colRemark);
						parameter.Value = (object)test.Remark??DBNull.Value;
						command.Parameters.Add(parameter);
		
					    parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}",coladdress);
						parameter.Value = (object)test.address??DBNull.Value;
						command.Parameters.Add(parameter);
		
			           
        }		
		
		private void AddUpdateParameters(DbCommand command, test test)
        {
		            AddIdentityColumn(command, test);
		            AddInsertParameters(command, test);
        }
				
        private void AddIdentityColumn(DbCommand command, test test)
        {
		  
            var parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", IdentityColumn);
            parameter.Value = test.Id;
            command.Parameters.Add(parameter);

		}
				
		private string GettSelectSQL()
        {
            var sql = string.Format(@" SELECT  {0} 
                                          FROM 
                                                test
                                       ", _columnCollectionSelectString);
            return sql;
        }      

		private string GetPagedQuery( string sqlText, int startIndex , int pageSize)
		{
		  	var endIndex = startIndex + pageSize;
          	startIndex = startIndex + 1;

          	var sql = string.Format(@"SELECT TOP {0} {4} 
						FROM ( 
							SELECT    {4} , ROW_NUMBER() OVER ({1}) AS ROW 
						    FROM  test ) AS WithRowNumber
					    WHERE
                            ROW BETWEEN {2} AND {3} {1}", pageSize, sqlText, startIndex, endIndex, _columnCollectionSelectString);

          	return sql;		    
		}
		
        private string InsertSQL()
        {
			var nonIdentColumns= ColumnCollections.Except(new[]{IdentityColumn}).ToList();
		 	var sql = string.Format(@" INSERT INTO
                            test (
                              {0}
                            )
                            VALUES
                            (
                                {1}
                            ) ;
                                       ", nonIdentColumns.JoinFormat(",","{0}"), nonIdentColumns.JoinFormat(",", "@{0}"));
                        sql = string.Format("{0} SET @{1} = SCOPE_IDENTITY()", sql, IdentityColumn);
                        return sql;
        }

        private string UpdatetSQL()
        {
		 	var sql = string.Format(@" UPDATE
                                 test
                            SET
                               {1}
                            WHERE 
                                  {0}
                                       ", WhereClause, ColumnCollections.Except(KeyColumns).ToArray().JoinFormat(" , ", "{0} = @{0}"));
            return sql;
        }

        private string DeletetSQL()
        { 
		 	var sql = string.Format(@" DELETE  FROM
                              test
                            WHERE 
                                {0}           
                                       ", string.Format("{0}=@{0}",IdentityColumn));
            return sql;
        }
  
	}
	}
 
